package com.yanzhang.dao.bill;

import com.mysql.cj.util.StringUtils;
import com.yanzhang.pojo.Bill;
import com.yanzhang.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yanzhang
 */
public class BillDaoImpl implements BillDao{
    /**
     * 增加一个账单
     * @param connection 数据库连接
     * @param bill 账单对象
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int add(Connection connection, Bill bill) throws Exception {
        int result = 0;
        if (connection != null && bill != null) {
            PreparedStatement preparedStatement = null;
            String sql = "insert into smbms_bill (billCode,productName,productDesc,productUnit,productCount,totalPrice,isPayment,providerId,createdBy,creationDate) values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {bill.getBillCode(),bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),bill.getProviderId(),bill.getCreatedBy(),bill.getCreationDate()};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 条件查询结果集合
     * @param connection 数据库连接
     * @param bill 账单对象
     * @return 账单集合
     * @throws Exception 抛出SQL异常
     */
    @Override
    public List<Bill> getBillList(Connection connection, Bill bill) throws Exception {
        List<Bill> bills = new ArrayList<>();
        if (connection == null) {
            return bills;
        }
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        StringBuilder sql = new StringBuilder();
        sql.append("select b.*, p.proName as providerName from smbms_bill b, smbms_provider p where b.providerId = p.id");
        List<Object> params = new ArrayList<>();
        if (bill != null) {
            if (!StringUtils.isNullOrEmpty(bill.getProductName())) {
                sql.append(" and b.productName like ?");
                params.add("%" + bill.getProductName() + "%");
            }
            if (bill.getProviderId() != null && bill.getProviderId() > 0) {
                sql.append(" and b.providerId = ?");
                params.add(bill.getProviderId());
            }
            if (bill.getIsPayment() != null && 0 < bill.getIsPayment() && bill.getIsPayment() < 3) {
                sql.append(" and b.isPayment = ?");
                params.add(bill.getIsPayment());
            }
        }
        rs = DBUtil.query(connection, preparedStatement, rs, sql.toString(), params.toArray());
        while (rs.next()) {
            bills.add(getBillByResult(rs));
        }
        DBUtil.closeResource(preparedStatement, rs);
        return bills;
    }

    /**
     * 根据id获取账单对象
     * @param connection 数据库连接
     * @param id 主键
     * @return 账单对象
     * @throws Exception 抛出SQL异常
     */
    @Override
    public Bill getBillById(Connection connection, int id) throws Exception {
        Bill bill = null;
        if (connection != null && id > 0) {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            String sql = "select b.*, p.proName as providerName from smbms_bill b, smbms_provider p where b.providerId = p.id and b.id = ?";
            Object[] params = {id};
            rs = DBUtil.query(connection, preparedStatement, rs, sql, params);
            if (rs.next()) {
                bill = getBillByResult(rs);
            }
            DBUtil.closeResource(preparedStatement, rs);
        }
        return bill;
    }

    /**
     * 修改账单信息
     * @param connection 数据库连接
     * @param bill 账单对象
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int modify(Connection connection, Bill bill) throws Exception {
        int result = 0;
        if (connection != null && bill != null) {
            PreparedStatement preparedStatement = null;
            String sql = "update smbms_bill set productName=?,productDesc=?,productUnit=?,productCount=?,totalPrice=?,isPayment=?,providerId=?,modifyBy=?,modifyDate=? where id = ?";
            Object[] params = {bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),bill.getProviderId(),bill.getModifyBy(),bill.getModifyDate(),bill.getId()};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 通过id删除账单
     * @param connection 数据库连接
     * @param id 主键
     * @return 结果数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int deleteBillById(Connection connection, int id) throws Exception {
        int result = 0;
        if (connection != null && id > 0) {
            PreparedStatement preparedStatement = null;
            String sql = "delete from smbms_bill where id = ?";
            Object[] params = {id};
            result = DBUtil.execute(connection, preparedStatement, sql, params);
            DBUtil.closeResource(preparedStatement, null);
        }
        return result;
    }

    /**
     * 根据供应商id查询账单数量
     * @param connection 数据库连接
     * @param providerId 供应商id
     * @return 数量
     * @throws Exception 抛出SQL异常
     */
    @Override
    public int getBillCountByProviderId(Connection connection, int providerId) throws Exception {
        int count = 0;
        if (connection != null && providerId > 0) {
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            String sql = "select count(1) as count from smbms_bill where providerId = ?";
            Object[] params = {providerId};
            rs = DBUtil.query(connection, preparedStatement, rs, sql, params);
            if (rs.next()) {
                count = rs.getInt("count");
            }
            DBUtil.closeResource(preparedStatement, rs);
        }
        return count;
    }


    /**
     * 根据查询结果获取账单对象
     * @param rs 查询结果
     * @return 账单对象
     * @throws Exception 抛出SQL异常
     */
    private Bill getBillByResult(ResultSet rs) throws Exception {
        Bill bill = new Bill();
        bill.setId(rs.getInt("id"));
        bill.setBillCode(rs.getString("billCode"));
        bill.setProductName(rs.getString("productName"));
        bill.setProductDesc(rs.getString("productDesc"));
        bill.setProductUnit(rs.getString("productUnit"));
        bill.setProductCount(rs.getBigDecimal("productCount"));
        bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
        bill.setIsPayment(rs.getInt("isPayment"));
        bill.setProviderId(rs.getInt("providerId"));
        bill.setProviderName(rs.getString("providerName"));
        bill.setCreationDate(rs.getTimestamp("creationDate"));
        bill.setCreatedBy(rs.getInt("createdBy"));
        return bill;
    }
}
